﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using DSheet.Core.Data;

namespace DSheet.Core.CommonData
{
    public class CommonDataService
    {
        public static Guid YearDimId = new Guid("{81393D12-07B7-4A42-82D0-6F22E8DAA1AA}");
        public static Guid DomainDimId = new Guid("{D24DE4D8-C052-4BBD-B31B-5D2883092640}");
        public static Guid OrgDimId = new Guid("{4CC71F1E-7400-4B8F-B5D4-3C31843EC879}");

        #region year
        public List<BYear> GetYears(DBContext context)
        {
            List<BYear> datas = new List<BYear>();

            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "select * from byear";
            command.CommandText = sql;

            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                BYear data = new BYear();
                data.Id = new Guid(Convert.ToString(reader["id"]));
                //data.Code = Convert.ToString(reader["code"]);
                data.Name = Convert.ToString(reader["name"]);
                datas.Add(data);
            }
            reader.Close();

            return datas;
        }

        public bool SaveYear(DBContext context, BYear year)
        {
            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "insert into byear (id,name) values (@id,@name)";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", year.Id));
            command.Parameters.Add(new SqlParameter("@name", year.Name));
            if (command.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }

        public bool SyncYearsToDim(DBContext context)
        {
            DimDataService dimService = new DimDataService();

            dimService.DeleteDimData(context, YearDimId);

            List<BYear> years = GetYears(context);
            DimData dimData = new DimData();
            dimData.Id = YearDimId;
            dimData.Code = YearDimId.ToString();
            dimData.Name = "Year";
            dimService.SaveDimData(context, dimData);

            foreach (var year in years)
            {
                DimItem dimItem = new DimItem();
                dimItem.Id = year.Id;
                dimItem.Name = year.Name;
                dimItem.Code = year.Id.ToString();
                dimData.Items.Add(dimItem);

                dimService.SaveDimItem(context, dimData.Id, Guid.Empty, dimItem);
            }
            return true;
        }
        #endregion

        #region domain
        public List<BDomain> GetDomains(DBContext context)
        {
            List<BDomain> datas = new List<BDomain>();

            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "select * from bdomain";
            command.CommandText = sql;

            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                BDomain data = new BDomain();
                data.Id = new Guid(Convert.ToString(reader["id"]));
                //data.Code = Convert.ToString(reader["code"]);
                data.Name = Convert.ToString(reader["name"]);
                data.ParentId = new Guid(Convert.ToString(reader["parentid"]));
                datas.Add(data);
            }
            reader.Close();

            return datas;
        }

        public BDomain GetDomain(DBContext context, Guid domainId)
        {
            BDomain data = null;
            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "select * from bdomain where id=@id";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", domainId));

            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                data = new BDomain();
                data.Id = new Guid(Convert.ToString(reader["id"]));
                //data.Code = Convert.ToString(reader["code"]);
                data.Name = Convert.ToString(reader["name"]);
                data.ParentId = new Guid(Convert.ToString(reader["parentid"]));
            }
            reader.Close();

            return data;
        }

        public bool SaveDomain(DBContext context, BDomain domain)
        {
            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "insert into bdomain (id,name,parentid) values (@id,@name,@parentid)";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", domain.Id));
            command.Parameters.Add(new SqlParameter("@name", domain.Name));
            command.Parameters.Add(new SqlParameter("@parentid", domain.ParentId));
            if (command.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }

        public bool SyncBDomainToDim(DBContext context)
        {
            DimDataService dimService = new DimDataService();

            dimService.DeleteDimData(context, DomainDimId);

            List<BDomain> domains = GetDomains(context);
            DimData dimData = new DimData();
            dimData.Id = DomainDimId;
            dimData.Code = DomainDimId.ToString();
            dimData.Name = "BudgetDomain";
            dimService.SaveDimData(context, dimData);

            foreach (var item in domains)
            {
                DimItem dimItem = new DimItem();
                dimItem.Id = item.Id;
                dimItem.Name = item.Name;
                dimItem.Code = item.Id.ToString();
                dimData.Items.Add(dimItem);

                dimService.SaveDimItem(context, dimData.Id, item.ParentId, dimItem);
            }
            return true;
        }
        #endregion

        #region org
        public List<BOrg> GetOrgs(DBContext context)
        {
            List<BOrg> datas = new List<BOrg>();

            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "select * from borg";
            command.CommandText = sql;

            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                BOrg data = new BOrg();
                data.Id = new Guid(Convert.ToString(reader["id"]));
                //data.Code = Convert.ToString(reader["code"]);
                data.Name = Convert.ToString(reader["name"]);
                data.ParentId = new Guid(Convert.ToString(reader["parentid"]));
                datas.Add(data);
            }
            reader.Close();

            return datas;
        }

        public BOrg GetOrg(DBContext context, Guid id)
        {
            BOrg data = null;
            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "select * from borg where id=@id";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", id));

            SqlDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                data = new BOrg();
                data.Id = new Guid(Convert.ToString(reader["id"]));
                //data.Code = Convert.ToString(reader["code"]);
                data.Name = Convert.ToString(reader["name"]);
                data.ParentId = new Guid(Convert.ToString(reader["parentid"]));
            }
            reader.Close();

            return data;
        }

        public bool SaveOrg(DBContext context, BOrg domain)
        {
            SqlCommand command = context.DBConnection.CreateCommand();
            string sql = "insert into borg (id,name,parentid) values (@id,@name,@parentid)";
            command.CommandText = sql;
            command.Parameters.Add(new SqlParameter("@id", domain.Id));
            command.Parameters.Add(new SqlParameter("@name", domain.Name));
            command.Parameters.Add(new SqlParameter("@parentid", domain.ParentId));
            if (command.ExecuteNonQuery() > 0)
            {
                return true;
            }
            return false;
        }

        public bool SyncBOrgToDim(DBContext context)
        {
            DimDataService dimService = new DimDataService();

            dimService.DeleteDimData(context, DomainDimId);

            List<BOrg> datas = GetOrgs(context);
            DimData dimData = new DimData();
            dimData.Id = OrgDimId;
            dimData.Code = OrgDimId.ToString();
            dimData.Name = "Org";
            dimService.SaveDimData(context, dimData);

            foreach (var item in datas)
            {
                DimItem dimItem = new DimItem();
                dimItem.Id = item.Id;
                dimItem.Name = item.Name;
                dimItem.Code = item.Id.ToString();
                dimData.Items.Add(dimItem);

                dimService.SaveDimItem(context, dimData.Id, item.ParentId, dimItem);
            }
            return true;
        }
        #endregion
    }
}
